-- @owner: wan005
-- @date: 2024-08-12
-- @testpoint: 在自定义函数中验证json函数和操作符

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step1：建表；expect：成功
drop table if exists t_json0053;
create table t_json0053(
col01 int,
col02 json not null);
insert into t_json0053 values(1,'{"id": "3", "name": "Barney"}'),
(2,'{"id": "4", "name": "Betty"}'),(3,'{"id": "2", "name": "Wilma"}'),
(10,'[3.65,10,"true","x","44"]'),(10,'[3.65,10,"true",17,[22,"y",66]]'),(10,'[3.65,10,"true",7,{"x":"y"}]'),
(20,'"qwi<>23，。"'),(20,'"123”“！#￥"'),(20,'"按时间JIwqe22"'),
(30,cast('true' as json)),(30,'null'),(30,cast('656964' as json)),(30,cast('56848.695' as json));

--step2：创建函数；expect：成功
create or replace function f_json0053(json,json) returns boolean
as $$
begin
return json_contains($1,$2);
end;
$$ language plpgsql
returns null on null input;
/

create or replace procedure p_json0053
as select_str national varchar;
begin
    select_str = 'select json_contains(col02, ''3'') from t_json0053;';
	execute immediate select_str;
end;
/

select f_json0053(col02,col02) from t_json0053;
call p_json0053;

--step3：清理环境；expect：清理成功
drop table if exists t_json0053;
drop function  f_json0053;
drop procedure p_json0053;
